﻿using ElectronicSelling.DataAccess;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;

namespace CMS.DataAccess
{
    public class DataAccess
    {
        //Các method dùng chung

        /// <summary>
        /// Execute stored procedure with parameter Non-return value
        /// </summary>
        /// <param name="storedName"></param>
        /// <param name="prms"></param>
        public static void ExeStoredWithParamNon(string storedName, params object[] prms)
        {
            SqlConnection conn = Connection.GetConnection();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.CommandText = storedName;
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < prms.Length; i += 2)
                {
                    SqlParameter param = new SqlParameter(prms[i].ToString(), prms[i + 1]);
                    cmd.Parameters.Add(param);
                }
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Execute stored procedure with parameter return bool value
        /// </summary>
        /// <param name="storedName"></param>
        /// <param name="prms"></param>
        /// <returns></returns>
        public static bool ExeStoredWithParamToBool(string storedName, params object[] prms)
        {
            SqlConnection conn = Connection.GetConnection();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.CommandText = storedName;
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < prms.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(prms[i].ToString(), prms[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Execute stored procedure non-return
        /// </summary>
        /// <param name="storedName"></param>
        public static void ExeStoredNon(string storedName)
        {
            SqlCommand cmd = new SqlCommand();
            //cmd.CommandTimeout = 2000;
            SqlConnection conn = Connection.GetConnection();
            cmd.Parameters.Clear();
            cmd.CommandText = storedName;
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Execute stored procedure non-parameter, return DataTable
        /// </summary>
        /// <param name="storedName"></param>
        /// <returns></returns>
        public static DataTable ExeStoredNonToDataTable(string storedName)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = Connection.GetConnection();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);
                conn.Close();
                cmd.Dispose();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
            return dt;
        }

        /// <summary>
        /// Execute stored procedure with parameter return DataTable
        /// </summary>
        /// <param name="storedName"></param>
        /// <param name="prms"></param>
        /// <returns></returns>
        public static DataTable ExeStoredWithParamToDataTable(string storedName, params object[] prms)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = Connection.GetConnection();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < prms.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(prms[i].ToString(), prms[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);
                conn.Close();
                cmd.Dispose();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
            return dt;
        }

        /// <summary>
        /// Execute stored procedure with parameter, return number of record
        /// </summary>
        /// <param name="storedName"></param>
        /// <param name="prms"></param>
        /// <returns></returns>
        public static int ExeStoredCount(string storedName, params object[] prms)
        {
            int count = 0;
            SqlConnection cn = Connection.GetConnection();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = cn;
                cn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < prms.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(prms[i].ToString(), prms[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                count = (int)cmd.ExecuteScalar();
                cn.Close();
                cmd.Dispose();
            }
            catch { }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return count;
        }

        /// <summary>
        /// Execute stored procedure with parameter, return DropDownList
        /// </summary>
        /// <param name="StoredName"></param>
        /// <param name="drl"></param>
        /// <param name="prms"></param>
        public static void ExeStoredToDropDown(string StoredName, DropDownList drl, params object[] prms)
        {
            SqlConnection conn = Connection.GetConnection();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader dr;
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = StoredName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < prms.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(prms[i].ToString(), prms[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    drl.Items.Add(Convert.ToString(dr.GetString(0)));
                }
                dr.Close();
                conn.Close();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Execute stored procedure with param, return decimal
        /// </summary>
        /// <param name="storedName"></param>
        /// <param name="prms"></param>
        /// <returns></returns>
        public static decimal ExeStoredToDecimal(string storedName, params object[] prms)
        {
            decimal data = 0;
            SqlConnection conn = Connection.GetConnection();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader dr;
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < prms.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(prms[i].ToString(), prms[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    data = Convert.ToDecimal(dr[0]);
                }
                dr.Close();
                conn.Close();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
            return data;
        }

        /// <summary>
        /// Execute stored procedure with param, return double value
        /// </summary>
        /// <param name="storedName"></param>
        /// <param name="prms"></param>
        /// <returns></returns>
        public static double ExeStoredToDouble(string storedName, params object[] prms)
        {
            double data = 0;
            SqlConnection conn = Connection.GetConnection();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader dr;
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < prms.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(prms[i].ToString(), prms[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    data = Convert.ToDouble(dr[0]);
                }
                dr.Close();
                conn.Close();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
            return data;
        }

        /// <summary>
        /// Execute stored procedure with param, return double value
        /// </summary>
        /// <param name="storedName"></param>
        /// <param name="prms"></param>
        /// <returns></returns>
        public static int ExeStoredToInt(string storedName, params object[] prms)
        {
            int data = 0;
            SqlConnection conn = Connection.GetConnection();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader dr;
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < prms.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(prms[i].ToString(), prms[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    data = Convert.ToInt16(dr[0]);
                }
                dr.Close();
                conn.Close();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
            return data;
        }

        /// <summary>
        /// Execute stored procedure with param, return string value
        /// </summary>
        /// <param name="storedName"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public static string ExeStoredToString(string storedName, params object[] para)
        {
            string data = "";
            SqlConnection conn = Connection.GetConnection();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader dr;
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < para.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(para[i].ToString(), para[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    data = dr[0].ToString();
                }
                dr.Close();
                conn.Close();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
            return data;
        }

        /// <summary>
        /// Execute stored procedure with param, return DateTime value
        /// </summary>
        /// <param name="storedName"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public static DateTime ExeStoredToDate(string storedName, params object[] para)
        {
            DateTime data = new DateTime();
            SqlConnection conn = Connection.GetConnection();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader dr;
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < para.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(para[i].ToString(), para[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    data = Convert.ToDateTime(dr[0]);
                }
                dr.Close();
                conn.Close();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
            return data;
        }

        public static int ExeSQLGetInt(string storedName)
        {
            int value = 0;
            SqlConnection conn = Connection.GetConnection();
            //try
            //{
            conn.Open();
            SqlCommand cmd = new SqlCommand(storedName, conn);
            value = (int)cmd.ExecuteScalar();
            conn.Close();
            cmd.Dispose();
            //}
            //catch { }
            //finally
            //{
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Dispose();
            //}
            return value;
        }

        //public static string ExeParamGetDataToString(string storedName, params object[] para)
        //{
        //    string data = "";
        //    SqlConnection cn = Connection.GetConnection();
        //    SqlCommand cmd = new SqlCommand();
        //    SqlDataReader dr;
        //    try
        //    {
        //        cmd.Connection = cn;
        //        cn.Open();
        //        cmd.CommandText = storedName;
        //        cmd.CommandType = CommandType.Text;
        //        for (int i = 0; i < para.Length; i += 2)
        //        {
        //            SqlParameter pa = new SqlParameter(para[i].ToString(), para[i + 1]);
        //            cmd.Parameters.Add(pa);
        //        }
        //        dr = cmd.ExecuteReader();
        //        while (dr.Read())
        //        {
        //            data = Convert.ToString(dr.GetString(0));
        //        }
        //        dr.Close();
        //        cn.Close();
        //    }
        //    catch { }
        //    finally
        //    {
        //        if (cn.State == ConnectionState.Open)
        //            cn.Close();
        //        cn.Dispose();
        //    }
        //    return data;
        //}
    }
}